Personal Computer World 2008 February/PCWFEB08.iso/Software/Shareware/DeviceLock 6.2.11337/setup.exe/Main/DLServer.exe/0/SQL/105

Text (UTF-16) | 2007-11-12 | 81.3 KB | 1,721 lines

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLAddDataDesc') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLAddDataDesc
GO
CREATE PROCEDURE DLAddDataDesc
    @ServerId int output,
    @DataSize bigint,
    @DigestSHA1 binary(20),
    @DigestMD5 binary(16),
    @CRC32 binary(4),
    @StoreId int output,
    @Location tinyint output

AS
    SET NOCOUNT ON
    DECLARE @ServerIdTmp int
    DECLARE @LocationTmp tinyint
    DECLARE @RealSize bigint

    SELECT TOP 1
        @ServerIdTmp = ServerId,
        @StoreId = StoreId,
        @LocationTmp = Location,
        @RealSize = RealSize
        FROM DLStore
        WHERE DigestSHA1 = @DigestSHA1
        AND DigestMD5 = @DigestMD5
        AND CRC32 = @CRC32
        AND DataSize = @DataSize

    IF @@ROWCOUNT > 0 BEGIN
        SET @ServerId = @ServerIdTmp
        SET @Location = @LocationTmp
        IF @RealSize = @DataSize
            RETURN 2
        ELSE
            RETURN 1
    END
    ELSE BEGIN
        INSERT INTO DLStore(ServerId, DataSize, DigestSHA1, DigestMD5, CRC32, Location)
        VALUES(@ServerId, @DataSize, @DigestSHA1, @DigestMD5, @CRC32, @Location)
        SET @StoreId = @@IDENTITY
        RETURN 0
    END
    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLAddStation') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLAddStation
GO
CREATE PROCEDURE DLAddStation
    @ServerId int,
    @NetworkAddr nvarchar(50),
    @Endpoint nvarchar(50),
    @CompId int output
AS
    SET NOCOUNT ON

    SELECT TOP 1 @CompId = CompId FROM
        DLStations
        WHERE ServerId = @ServerId
        AND NetworkAddr = @NetworkAddr

    IF @@ROWCOUNT > 0 BEGIN
        UPDATE DLStations
            SET Endpoint = @Endpoint, NeedToScan = 1, AttemptsCount = 0
            WHERE ServerId = @ServerId
            AND CompId = @CompId
        RETURN 1
    END
    ELSE BEGIN
        INSERT INTO DLStations
        (ServerId, NetworkAddr, Endpoint, NeedToScan, AttemptsCount)
            VALUES (@ServerId, @NetworkAddr, @Endpoint, 1, 0)
        SET @CompId = @@IDENTITY
        RETURN 0
    END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLAddUser') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLAddUser
GO
CREATE PROCEDURE DLAddUser
    @ServerId int,
    @SID nvarchar(255),
    @UserName nvarchar(50) = NULL,
    @ShadowId int = NULL,
    @Flag int = 0,
    @UserId int = NULL output
AS
    SET NOCOUNT ON
    DECLARE @Ret int
    SELECT TOP 1 @UserId = UserId FROM
        DLUsers
        WHERE ServerId = @ServerId
        AND SID = @SID

    IF @@ROWCOUNT > 0 BEGIN
        IF @UserName IS NOT NULL BEGIN
            UPDATE DLUsers
                SET UserName = @UserName
                WHERE ServerId = @ServerId AND
                UserId = @UserId
        END
        SET @Ret = 1
    END
    ELSE BEGIN
        IF @UserName IS NULL
            SET @UserName = @SID

        INSERT INTO DLUsers (ServerId, SID, UserName)
            VALUES (@ServerId, @SID, @UserName)
        SET @UserId = @@IDENTITY
        SET @Ret = 0
    END
    IF @ShadowId IS NOT NULL AND @UserId IS NOT NULL AND @Flag IS NOT NULL BEGIN
        INSERT INTO DLShadowFiles_Users(ServerId, ShadowId, UserId, Flag)
        SELECT @ServerId, @ShadowId, @UserId, @Flag
        WHERE NOT EXISTS (
            SELECT *
            FROM DLShadowFiles_Users
            WHERE ServerId = @ServerId
            AND ShadowId = @ShadowId
            AND UserId = @UserId)
    END
    RETURN @Ret
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLDelData') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLDelData
GO
CREATE PROCEDURE DLDelData
    @ServerId int,
    @ShadowId int
AS
    SET NOCOUNT ON

    DECLARE @StoreId int
    DECLARE @ShadowCount int

    SELECT @StoreId = StoreId FROM DLShadowFiles
    WHERE ServerId = @ServerId
        AND ShadowId = @ShadowId
    IF @@ROWCOUNT = 0
        RETURN 0

    SELECT @ShadowCount = COUNT(ShadowId) FROM DLShadowFiles
    WHERE ServerId = @ServerId
        AND StoreId = @StoreId

    IF @ShadowCount > 1 BEGIN
        UPDATE DLShadowFiles
        SET StoreId = NULL
        WHERE ServerId = @ServerId
            AND ShadowId = @ShadowId
        RETURN 0
    END

    DELETE FROM DLStoreBin
    WHERE ServerId = @ServerId
        AND StoreId = @StoreId

    SET NOCOUNT OFF
    SELECT Url FROM DLStoreUrl
    WHERE ServerId = @ServerId
        AND StoreId = @StoreId
    SET NOCOUNT ON

    DELETE FROM DLStoreUrl
    WHERE ServerId = @ServerId
        AND StoreId = @StoreId

    UPDATE DLShadowFiles
    SET StoreId = NULL
    WHERE ServerId = @ServerId
        AND ShadowId = @ShadowId

    DELETE FROM DLStore
    WHERE ServerId = @ServerId
        AND StoreId = @StoreId

GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLDelUser') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLDelUser
GO
CREATE PROCEDURE DLDelUser
    @ServerId int,
    @UserId int
AS
    SET NOCOUNT ON

    DELETE FROM DLUsers
    WHERE ServerId = @ServerId
        AND UserId = @UserId
        AND NOT EXISTS (
            SELECT * FROM DLShadowFiles_Users
            WHERE ServerId = @ServerId
                AND UserId = @UserId)
        AND NOT EXISTS (
            SELECT * FROM DLShadowFiles
            WHERE ServerId = @ServerId
                AND DelUserId = @UserId)
        AND NOT EXISTS (
            SELECT * FROM DLAuditLog
            WHERE ServerId = @ServerId
                AND UserId = @UserId)

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetDataPart') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetDataPart
GO
CREATE PROCEDURE DLGetDataPart
    @ServerId int,
    @ShadowId int,
    @PosFrom bigint,
    @SizeToGet bigint
AS
    SET NOCOUNT ON

    IF @PosFrom < 0 OR @SizeToGet <= 0
        RETURN 1
    DECLARE @StoreId int

    SELECT @StoreId = StoreId
    FROM DLShadowFiles
    WHERE ServerId = @ServerId AND ShadowId = @ShadowId

    IF @@ROWCOUNT = 0
        RETURN 1

    DECLARE @CurDataLen int
    DECLARE @CurPartId int
    DECLARE @CurPos bigint
    DECLARE @LocalPos int
    DECLARE @LocalSize int
    DECLARE @SizeCollected bigint

    DECLARE @FetchSize int

    DECLARE DataCur CURSOR FAST_FORWARD FOR
        SELECT PartId, DATALENGTH(Data)
        FROM DLStoreBin
        WHERE ServerId = @ServerId
        AND StoreId = @StoreId
        AND Data IS NOT NULL
        ORDER BY PartId ASC
    OPEN DataCur
    SELECT @CurPos = 0, @SizeCollected = 0, @CurPartId = 0, @CurDataLen = 0, @SizeCollected = 0

    SET TEXTSIZE 8000
    WHILE 1=1 BEGIN

        SET @CurPos = @CurPos + @CurDataLen

        FETCH FROM DataCur INTO @CurPartId, @CurDataLen

        IF @@fetch_status=-1
            BREAK
        IF @@fetch_status=-2
            CONTINUE

        IF @CurPos >= @PosFrom + @SizeToGet
            BREAK
        IF @CurPos + @CurDataLen <= @PosFrom
            CONTINUE

        IF @CurPos < @PosFrom
            SET @LocalPos = @PosFrom - @CurPos
        ELSE
            SET @LocalPos = 0

        IF @SizeToGet - @SizeCollected < @CurDataLen - @LocalPos
            SET @LocalSize = @SizeToGet - @SizeCollected
        ELSE
            SET @LocalSize = @CurDataLen - @LocalPos

        WHILE @LocalSize > 0 BEGIN
            IF @LocalSize > 8000
                SET @FetchSize = 8000
            ELSE
                SET @FetchSize = @LocalSize
            SELECT SUBSTRING(Data, @LocalPos + 1, @FetchSize)
            FROM DLStoreBin
            WHERE ServerId = @ServerId
                AND StoreId = @StoreId
                AND PartId = @CurPartId

            SET @LocalPos = @LocalPos + @FetchSize
            SET @LocalSize = @LocalSize - @FetchSize
            SET @SizeCollected = @SizeCollected - @FetchSize
        END
    END
    SET TEXTSIZE 0

    CLOSE DataCur
    DEALLOCATE DataCur

    RETURN 0;
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetDataRealSize') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetDataRealSize
GO
CREATE PROCEDURE DLGetDataRealSize
    @ServerId int,
    @StoreId int,
    @DataSize bigint output
AS
    SET NOCOUNT ON

    IF EXISTS (SELECT TOP 1 StoreId FROM DLStoreBin
        WHERE ServerId = @ServerId AND StoreId = @StoreId)
    BEGIN
        SELECT @DataSize = SUM(CAST(DATALENGTH(Data) AS bigint))
        FROM DLStoreBin
        WHERE ServerId = @ServerId AND StoreId = @StoreId
        RETURN 0;
    END ELSE
        RETURN 1;
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetDataUrl') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetDataUrl
GO
CREATE PROCEDURE DLGetDataUrl
    @ServerId int,
    @ShadowId int
AS
    SET NOCOUNT ON
    DECLARE @StoreId int

    SELECT @StoreId = StoreId
    FROM DLShadowFiles
    WHERE ServerId = @ServerId AND ShadowId = @ShadowId

    IF @@ROWCOUNT = 0
        RETURN 1

    SET NOCOUNT OFF

    SELECT Url FROM DLStoreUrl
        WHERE ServerId = @ServerId AND StoreId = @StoreId

    SET NOCOUNT ON

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetFieldById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetFieldById
GO
CREATE PROCEDURE DLGetFieldById
    @QueryId int,
    @FieldId int,
    @FieldFullName nvarchar(200) output
AS
    SET NOCOUNT ON
    DECLARE @Ret int
    SET @Ret = 0
    SET @FieldFullName =
    CASE @QueryId
        WHEN 0 THEN CASE @FieldId
            WHEN 0 THEN N'sf.Status'
            WHEN 1 THEN N'st.NetworkAddr'
            WHEN 2 THEN N'us.UserName'
            WHEN 3 THEN N'sf.OriginalFileName'
            WHEN 4 THEN N'sf.ShadowSize'
            WHEN 5 THEN N'sf.StartingOffset'
            WHEN 6 THEN N'sf.CreationDate'
            WHEN 7 THEN N'sf.ProcessName'
            WHEN 8 THEN N'sf.Pid'
            WHEN 9 THEN N'sf.DeviceType'
            WHEN 10 THEN N'sf.OperationType'
            ELSE NULL
        END
        WHEN 1 THEN CASE @FieldId
            WHEN 0 THEN N'ev.Severity'
            WHEN 1 THEN N'ev.LogTime'
            WHEN 2 THEN N'ev.EventCode'
            WHEN 3 THEN N'ev.Message'
            WHEN 4 THEN N'sv.Url'
            WHEN 5 THEN N'ev.EventId'
            ELSE NULL
        END
        WHEN 2 THEN CASE @FieldId
            WHEN 0 THEN N'au.Type'
            WHEN 1 THEN N'st.NetworkAddr'
            WHEN 2 THEN N'au.CreationDate'
            WHEN 3 THEN N'au.DeviceType'
            WHEN 4 THEN N'au.Action'
            WHEN 5 THEN N'au.Name'
            WHEN 6 THEN N'au.Info'
            WHEN 7 THEN N'us.UserName'
            WHEN 8 THEN N'au.Pid'
            WHEN 9 THEN N'au.ProcessName'
            WHEN 10 THEN N'au.EventId'
            ELSE NULL
        END
        ELSE NULL
    END

    IF @FieldFullName IS NULL BEGIN
        SET @Ret = CASE @QueryId
            WHEN 0 THEN 10
            WHEN 1 THEN 5
            WHEN 2 THEN 10
            ELSE -1
        END
    END    ELSE
        RETURN 0

    RETURN @Ret
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetQueuedStations') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetQueuedStations
GO
CREATE PROCEDURE DLGetQueuedStations
    @ServerId int
AS
    SELECT CompId
    FROM DLStations
    WHERE ServerId = @ServerId
    AND NeedToScan = 1

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetServerLocalId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetServerLocalId
GO
CREATE PROCEDURE DLGetServerLocalId
    @ServerGuid uniqueidentifier,
    @ServerUrl    nvarchar(500) = NULL,
    @ServerId int output
AS
    SET NOCOUNT ON

    SELECT @ServerId = ServerId
    FROM DLServers
    WHERE ServerGuid = @ServerGuid

    IF @@ROWCOUNT > 0 BEGIN
        IF @ServerUrl IS NOT NULL
            UPDATE DLServers
            SET Url = @ServerUrl
            WHERE ServerId = @ServerId
    END
    ELSE BEGIN
        INSERT INTO DLServers(ServerGuid, Url)
        VALUES (@ServerGuid, @ServerUrl)
        SET @ServerId = @@IDENTITY
    END
    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetStationAddr') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetStationAddr
GO
CREATE PROCEDURE DLGetStationAddr
    @ServerId int,
    @CompId int,
    @NetworkAddr nvarchar(50) output,
    @Endpoint nvarchar(50) output
AS
    SET NOCOUNT ON
    SELECT @NetworkAddr = NetworkAddr, @Endpoint = Endpoint FROM
        DLStations
        WHERE ServerId = @ServerId
        AND CompId = @CompId
    IF @@ROWCOUNT > 0
        RETURN 0
    ELSE
        RETURN 1
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLSetDataRealSize') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLSetDataRealSize
GO
CREATE PROCEDURE DLSetDataRealSize
    @ServerId int,
    @StoreId int,
    @RealSize bigint
AS
    SET NOCOUNT ON
    UPDATE DLStore
        SET RealSize = @RealSize
        WHERE ServerId = @ServerId
        AND StoreId = @StoreId
    IF @@ERROR <> 0
        RETURN 1
    ELSE
        RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLSetDataUrl') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLSetDataUrl
GO
CREATE PROCEDURE DLSetDataUrl
    @ServerId int,
    @StoreId int,
    @Url ntext
AS
    IF EXISTS (
        SELECT * FROM DLStoreUrl
        WHERE ServerId = @ServerId
        AND StoreId = @StoreId) BEGIN
        UPDATE DLStoreUrl
            SET Url = @Url
            WHERE ServerId = @ServerId
            AND StoreId = @StoreId
        RETURN 1
    END
    ELSE BEGIN
        INSERT INTO DLStoreUrl(ServerId, StoreId, Url)
            VALUES(@ServerId, @StoreId, @Url)
        RETURN 0
    END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLStationScaned') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLStationScaned
GO
CREATE PROCEDURE DLStationScaned
    @ServerId int,
    @CompId int,
    @MaxAttempts int,
    @Retry int = 0 output
AS
    SET NOCOUNT ON
    IF @Retry > 0 BEGIN
        DECLARE @Attempts int

        SELECT @Attempts = AttemptsCount
        FROM DLStations
        WHERE ServerId = @ServerId
            AND CompId = @CompId

        IF @@ROWCOUNT = 0
            RETURN 1

        IF @MaxAttempts = 0 OR @Attempts < @MaxAttempts    BEGIN
            SET @Attempts = @Attempts + 1
            SET @Retry = @MaxAttempts - @Attempts

            UPDATE DLStations
                SET AttemptsCount = @Attempts
                WHERE ServerId = @ServerId
                AND CompId = @CompId
        END
        IF @MaxAttempts <> 0 AND @Attempts >= @MaxAttempts BEGIN
            SET @Retry = 0
            UPDATE DLStations
                SET NeedToScan = 0
                WHERE ServerId = @ServerId
                AND CompId = @CompId
        END
    END
    ELSE BEGIN
        UPDATE DLStations
            SET NeedToScan = 0,
                LastScanTime = GETUTCDATE()
            WHERE ServerId = @ServerId
            AND CompId = @CompId
        IF @@ROWCOUNT = 0
            RETURN 1
    END
    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLFilterGen') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLFilterGen
GO
CREATE PROCEDURE DLFilterGen
    @QueryId int,
    @Condition nvarchar(4000) output
AS
    SET NOCOUNT ON
    DECLARE @Ret int
    DECLARE @FieldId int
    DECLARE @FieldName nvarchar(200)

    SET @Condition = LTRIM(@Condition)
    SET @Condition = RTRIM(@Condition)

    IF @Condition IS NULL OR @Condition = N''
        SET @Condition = N''
    ELSE BEGIN
        EXEC @FieldId = DLGetFieldById @QueryId, -1, @FieldName output

        WHILE @FieldId >= 0 BEGIN
            EXEC @Ret = DLGetFieldById @QueryId, @FieldId, @FieldName output
            IF @Ret <> 0 OR @FieldName IS NULL
                BREAK
            SET @Condition = REPLACE(@Condition, CAST(@FieldId AS nvarchar(2))+N'?', @FieldName)
            SET @FieldId = @FieldId - 1
        END
        SET @Condition = REPLACE(@Condition, N'\?', N'?')
        SET @Condition = REPLACE(@Condition, N'\\', N'\')
    END

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetOption') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetOption
GO
CREATE PROCEDURE DLGetOption
    @Name nvarchar(50),
    @Value nvarchar(50) output,
    @ServerId int = NULL,
    @CompId int = NULL
AS
    SET NOCOUNT ON

    SELECT TOP 1 @Value = Value
    FROM DLSettings
    WHERE [Name] = @Name
    AND ISNULL(ServerId, -1) = ISNULL(@ServerId, -1)
    AND ISNULL(CompId, -1) = ISNULL(@CompId, -1)

    IF @@ROWCOUNT > 0
        RETURN 0

    RETURN 1
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLSetOption') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLSetOption
GO
CREATE PROCEDURE DLSetOption
    @Name nvarchar(50),
    @Value nvarchar(50),
    @ServerId int = NULL,
    @CompId int = NULL
AS
    SET NOCOUNT ON

    DECLARE @TmpValue nvarchar(50)
    DECLARE @Ret int

    EXEC @Ret = DLGetOption @Name, @TmpValue output, @ServerId, @CompId

    IF @Ret = 0 BEGIN
        IF @Value = @TmpValue
            RETURN 0

        IF @Value IS NULL BEGIN
            DELETE FROM DLSettings
            WHERE [Name] = @Name
            AND ISNULL(ServerId, -1) = ISNULL(@ServerId, -1)
            AND ISNULL(CompId, -1) = ISNULL(@CompId, -1)
            RETURN 2
        END

        UPDATE DLSettings
        SET Value = @Value
        WHERE [Name] = @Name
        AND ISNULL(ServerId, -1) = ISNULL(@ServerId, -1)
        AND ISNULL(CompId, -1) = ISNULL(@CompId, -1)

        RETURN 0
    END
    ELSE BEGIN
        INSERT INTO DLSettings([Name], Value, ServerId, CompId)
        VALUES(@Name, @Value, @ServerId, @CompId)

        RETURN 1
    END

    RETURN 1
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLAddAuditRecord') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLAddAuditRecord
GO

CREATE PROCEDURE DLAddAuditRecord
    @ServerId int,
    @CompId int,
    @SID nvarchar(255),
    @UserName nvarchar(50),
    @CreationDate bigint,
    @EventId int,
    @Type smallint,
    @ProcessName nvarchar (1500),
    @Pid int,
    @DeviceType nvarchar (500),
    @Action nvarchar (500),
    @Name nvarchar (500),
    @Info nvarchar (500),
    @CustomData ntext,
    @RecordId int = NULL output
AS
    SET NOCOUNT ON

    DECLARE @UserId int

    SET @UserId = NULL

    IF @SID IS NOT NULL
        EXEC DLAddUser @ServerId, @SID, @UserName, NULL, NULL, @UserId output

    INSERT INTO DLAuditLog (ServerId, CompId, UserId, CreationDate, EventId,
                            Type, ProcessName, Pid, DeviceType, [Action],
                            [Name], Info, CustomData)
    VALUES (@ServerId, @CompId, @UserId, @CreationDate, @EventId,
            @Type, @ProcessName, @Pid, @DeviceType, @Action,
            @Name, @Info, @CustomData)

    SET @RecordId = @@IDENTITY

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLClearAuditLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLClearAuditLog
GO

CREATE PROCEDURE DLClearAuditLog
    @NRecords int output
AS
    SET NOCOUNT ON
    DECLARE @ident int

    BEGIN TRAN

    SELECT @NRecords = COUNT(*)
    FROM DLAuditLog

    SET @ident = IDENT_CURRENT('DLAuditLog')
    TRUNCATE TABLE DLAuditLog
    DBCC CHECKIDENT ('DLAuditLog', RESEED, @ident)

    COMMIT

GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLFilterAuditLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLFilterAuditLog
GO
CREATE PROCEDURE DLFilterAuditLog
    @Condition nvarchar(2000),
    @SortField int = NULL,
    @SortDir bit = 0,
    @PosFrom int = 0,
    @RecCount int = 0

AS
    SET NOCOUNT ON
    DECLARE @Ret int
    DECLARE @FieldName nvarchar(200)
    DECLARE @Query nvarchar(4000)
    DECLARE @SortOrder nvarchar(5)
    DECLARE @SortBackOrder nvarchar(5)
    DECLARE @RecordsCount int

    SET @Query = @Condition
    EXEC DLFilterGen 2, @Query output

    IF @Query <> N''
        SET @Query = N'WHERE ' + @Query

    SET @Query =
    N'FROM DLAuditLog AS au
    INNER JOIN DLStations AS st
        ON au.ServerId = st.ServerId
        AND au.CompId = st.CompId
    LEFT OUTER JOIN DLUsers AS us
        ON au.ServerId = us.ServerId
        AND au.UserId = us.UserId ' + @Query

    IF @SortField IS NULL BEGIN
        SET @Query = N'SELECT COUNT(DISTINCT au.RecordId) ' + @Query
        GOTO exec_query
    END

    EXEC @Ret = DLGetFieldById 2, @SortField, @FieldName output

    IF @Ret <> 0 SET @FieldName = N'1'

    IF ISNULL(@SortDir, 0) = 0 BEGIN
        SET @SortOrder = 'ASC'
        SET @SortBackOrder = 'DESC'
    END
    ELSE BEGIN
        SET @SortOrder = 'DESC'
        SET @SortBackOrder = 'ASC'
    END

    SET @RecordsCount = (SELECT COUNT(*) FROM DLAuditLog)

    IF ISNULL(@PosFrom, 0) <= 0
        SET @PosFrom = 1

    IF ISNULL(@RecCount, 0) < 0
        RETURN 1

    IF @PosFrom > @RecordsCount BEGIN
        SET @RecCount = 0
    END
    ELSE IF @PosFrom + @RecCount > @RecordsCount BEGIN
        SET @RecCount = @RecordsCount - @PosFrom + 1
    END

    SET @Query =
    N'au.ServerId AS ServerId,
    au.RecordId AS RecordId,
    st.NetworkAddr AS NetworkAddr,
    us.UserName AS UserName,
    us.SID AS SID,
    ' + @FieldName + N' AS SortField ' + @Query

    IF @RecCount IS NULL BEGIN
        SET @Query = N'SELECT DISTINCT ' + @Query

        SET @Query = N'SELECT S1.ServerId AS ServerId,
                    S1.RecordId AS RecordId,
                    au.EventId AS EventId,
                    au.Type AS Type,
                    au.CreationDate AS CreationDate,
                    au.Pid AS Pid,
                    S1.NetworkAddr AS NetworkAddr,
                    au.DeviceType AS DeviceType,
                    au.Action AS Action,
                    au.Name AS Name,
                    au.Info AS Info,
                    S1.UserName AS UserName,
                    S1.SID AS SID,
                    au.ProcessName AS ProcessName,
                    au.CustomData AS CustomData
                    FROM (' + @Query + N') AS S1,
                    DLAuditLog as au
                    WHERE S1.ServerId = au.ServerId
                    AND S1.RecordId = au.RecordId
                    ORDER BY S1.SortField ' + @SortOrder +
                    N', S1.RecordId ' + @SortOrder
    END
    ELSE BEGIN
        SET @Query = N'SELECT DISTINCT TOP ' + CAST(@PosFrom+@RecCount-1 AS nvarchar(10)) + N' ' +
                    @Query +
                    N' ORDER BY ' + @FieldName + N' ' + @SortOrder +
                    N', au.RecordId ' + @SortOrder


        SET @Query = N'SELECT TOP '+ CAST(@RecCount AS nvarchar(10)) + N' *
                    FROM (' + @Query + N') AS S1
                    ORDER BY SortField ' + @SortBackOrder +
                    N', RecordId ' + @SortBackOrder


        SET @Query = N'SELECT S2.ServerId AS ServerId,
                    S2.RecordId AS RecordId,
                    au.EventId AS EventId,
                    au.Type AS Type,
                    au.CreationDate AS CreationDate,
                    au.Pid AS Pid,
                    S2.NetworkAddr AS NetworkAddr,
                    au.DeviceType AS DeviceType,
                    au.Action AS Action,
                    au.Name AS Name,
                    au.Info AS Info,
                    S2.UserName AS UserName,
                    S2.SID AS SID,
                    au.ProcessName AS ProcessName,
                    au.CustomData AS CustomData
                    FROM (' + @Query + N') AS S2,
                    DLAuditLog as au
                    WHERE S2.ServerId = au.ServerId
                    AND S2.RecordId = au.RecordId
                    ORDER BY S2.SortField' + N' '+ @SortOrder +
                    N', S2.RecordId ' + @SortOrder
    END

exec_query:
    SET NOCOUNT OFF
    EXEC(@Query)
    SET NOCOUNT ON
    IF @@ERROR <> 0
        RETURN 1
    RETURN 0
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLPreAddAuditLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLPreAddAuditLog
GO

CREATE PROCEDURE DLPreAddAuditLog
    @Size int,
    @NeedDelete int,
    @TimeBefore bigint = NULL
AS
    SET NOCOUNT ON
    DECLARE @RecordsCount int
    DECLARE @DeleteCount int
    DECLARE @ServerId int
    DECLARE @RecordId int
    DECLARE @Ret int

    SET @Ret = 0

    SELECT @RecordsCount = COUNT(*) FROM DLAuditLog

    IF @RecordsCount <= @Size
        RETURN 1

    IF @NeedDelete = 0
        RETURN @Ret

    SET @DeleteCount = @RecordsCount - @Size


    IF @TimeBefore IS NULL BEGIN
        DECLARE OverwriteCur CURSOR FOR
            SELECT ServerId, RecordId FROM DLAuditLog
            ORDER BY CreationDate ASC, RecordId ASC
    END
    ELSE BEGIN
        DECLARE OverwriteCur CURSOR FOR
            SELECT ServerId, RecordId FROM DLAuditLog
            WHERE CreationDate < @TimeBefore
            ORDER BY CreationDate ASC, RecordId ASC
    END

    OPEN OverwriteCur

    WHILE @DeleteCount > 0 BEGIN
         FETCH NEXT FROM OverwriteCur INTO @ServerId, @RecordId
        IF @@fetch_status = -1
            BREAK
        SET @DeleteCount = @DeleteCount - 1

        IF @@fetch_status = -2
            CONTINUE

        DELETE DLAuditLog WHERE ServerId = @ServerId AND RecordId = @RecordId

        IF @@ROWCOUNT > 0
            SET @Ret = @Ret | 2

    END

    CLOSE OverwriteCur
    DEALLOCATE OverwriteCur

    IF @DeleteCount <= 0
        SET @Ret = @Ret | 1

    RETURN @Ret
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLLastAuditRecordTime') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLLastAuditRecordTime
GO

CREATE PROCEDURE DLLastAuditRecordTime
    @CompId int,
    @Timestamp bigint output
AS
    SET NOCOUNT ON

    SELECT @Timestamp = MAX(CreationDate)
    FROM DLAuditLog
    WHERE CompId = @CompId

    SET @Timestamp = ISNULL(@Timestamp, 0)

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLClearServerLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLClearServerLog
GO
CREATE PROCEDURE DLClearServerLog
    @NRecords int output
AS
    SET NOCOUNT ON
    DECLARE @ident int

    BEGIN TRAN

    SELECT @NRecords = COUNT(*)
    FROM DLEvents

    SET @ident = IDENT_CURRENT('DLEvents')
    TRUNCATE TABLE DLEvents
    DBCC CHECKIDENT ('DLEvents', RESEED, @ident)

    COMMIT
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLFilterServerLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLFilterServerLog
GO
CREATE PROCEDURE DLFilterServerLog
    @Condition nvarchar(2000),
    @SortField int = NULL,
    @SortDir bit = 0,
    @PosFrom int = 0,
    @RecCount int = 0

AS
    SET NOCOUNT ON
    DECLARE @Ret int
    DECLARE @FieldName nvarchar(200)
    DECLARE @Query nvarchar(4000)
    DECLARE @SortOrder nvarchar(5)
    DECLARE @SortBackOrder nvarchar(5)
    DECLARE @RecordsCount int
    DECLARE @IndexField int

    SET @IndexField = 5


    SET @Query = @Condition

    EXEC DLFilterGen 1, @Query output

    IF @Query <> N''
        SET @Query = N'WHERE ' + @Query

    SET @Query =
    N'FROM DLEvents AS ev
    INNER JOIN DLServers AS sv
        ON sv.ServerId = ev.ServerId ' + @Query

    IF @SortField IS NULL BEGIN
        SET @Query = N'SELECT COUNT(DISTINCT ev.EventId) ' + @Query
        GOTO exec_query
    END

    IF @SortField <> @IndexField
        EXEC @Ret = DLGetFieldById 1, @SortField, @FieldName output
    ELSE
        SET @Ret = 1

    IF @Ret <> 0 SET @FieldName = N'1'

    IF ISNULL(@SortDir, 0) = 0 BEGIN
        SET @SortOrder = 'ASC'
        SET @SortBackOrder = 'DESC'
    END
    ELSE BEGIN
        SET @SortOrder = 'DESC'
        SET @SortBackOrder = 'ASC'
    END

    SET @RecordsCount = (SELECT COUNT(*) FROM DLEvents)

    IF ISNULL(@PosFrom, 0) <= 0
        SET @PosFrom = 1

    IF ISNULL(@RecCount, 0) < 0
        RETURN 1

    IF @PosFrom > @RecordsCount BEGIN
        SET @RecCount = 0
    END
    ELSE IF @PosFrom + @RecCount > @RecordsCount BEGIN
        SET @RecCount = @RecordsCount - @PosFrom + 1
    END

    SET @Query =
    N'ev.EventId AS EventId,
    ev.Severity AS Severity,
    ev.EventCode AS EventCode,
    ev.LogTime AS LogTime,
    sv.Url AS Server,
    ev.Message AS Message,
    ' + @FieldName + N' AS SortField ' + @Query

    IF @RecCount IS NULL BEGIN
        SET @Query = N'SELECT DISTINCT ' + @Query

        SET @Query = N'SELECT S1.EventId AS EventId,
                    S1.Severity AS Severity,
                    S1.EventCode AS EventCode,
                    S1.LogTime AS LogTime,
                    S1.Server AS Server,
                    S1.Message AS Message
                    FROM (' + @Query + N') AS S1
                    ORDER BY S1.SortField ' + @SortOrder +
                    N', S1.EventId ' + @SortOrder
    END
    ELSE BEGIN
        SET @Query = N'SELECT DISTINCT TOP ' + CAST(@PosFrom+@RecCount-1 AS nvarchar(10)) + N' ' +
                    @Query +
                    N' ORDER BY SortField ' + @SortOrder +
                    N', ev.EventId ' + @SortOrder

        SET @Query = N'SELECT TOP '+ CAST(@RecCount AS nvarchar(10)) + N' *
                    FROM (' + @Query + N') AS S1
                    ORDER BY SortField ' + @SortBackOrder +
                    N', EventId ' + @SortBackOrder

        SET @Query = N'SELECT S2.EventId AS EventId,
                    S2.Severity AS Severity,
                    S2.EventCode AS EventCode,
                    S2.LogTime AS LogTime,
                    S2.Server AS Server,
                    S2.Message AS Message
                    FROM (' + @Query + N') AS S2
                    ORDER BY S2.SortField' + N' '+ @SortOrder +
                    N', S2.EventId ' + @SortOrder
    END

exec_query:
    SET NOCOUNT OFF
    EXEC(@Query)
    print @Query
    SET NOCOUNT ON
    IF @@ERROR <> 0
        RETURN 1
    RETURN 0
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLLogEvent') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLLogEvent
GO
CREATE PROCEDURE DLLogEvent
    @ServerId int,
    @Severity int,
    @EventCode int,
    @Message nvarchar(1000)
AS
    SET NOCOUNT ON

    INSERT INTO DLEvents(ServerId, LogTime, Severity, EventCode, Message)
    VALUES(@ServerId, GETUTCDATE(), @Severity, @EventCode, @Message)

GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLPreAddServerLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLPreAddServerLog
GO

CREATE PROCEDURE DLPreAddServerLog
    @Size int,
    @NeedDelete int,
    @DaysBefore int = NULL
AS
    SET NOCOUNT ON
    DECLARE @RecordsCount int
    DECLARE @DeleteCount int
    DECLARE @ServerId int
    DECLARE @EventId int
    DECLARE @Ret int

    SET @Ret = 0

    SELECT @RecordsCount = COUNT(*) FROM DLEvents

    IF @RecordsCount <= @Size
        RETURN 1

    IF @NeedDelete = 0
        RETURN @Ret

    SET @DeleteCount = @RecordsCount - @Size


    IF @DaysBefore IS NULL BEGIN
        DECLARE OverwriteCur CURSOR FOR
            SELECT ServerId, EventId FROM DLEvents
            ORDER BY LogTime ASC, EventId ASC
    END
    ELSE BEGIN
        DECLARE OverwriteCur CURSOR FOR
            SELECT ServerId, EventId FROM DLEvents
            WHERE LogTime < DATEADD(dd, -@DaysBefore, GETUTCDATE())
            ORDER BY LogTime ASC, EventId ASC
    END

    OPEN OverwriteCur

    WHILE @DeleteCount > 0 BEGIN
         FETCH NEXT FROM OverwriteCur INTO @ServerId, @EventId
        IF @@fetch_status=-1
            BREAK
        SET @DeleteCount = @DeleteCount - 1

        IF @@fetch_status=-2
            CONTINUE

        DELETE DLEvents WHERE ServerId = @ServerId AND EventId = @EventId

        IF @@ROWCOUNT > 0
            SET @Ret = @Ret | 2
    END

    CLOSE OverwriteCur
    DEALLOCATE OverwriteCur

    IF @DeleteCount <= 0
        SET @Ret = @Ret | 1

    RETURN @Ret

GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLAddShadowFile') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLAddShadowFile
GO
CREATE PROCEDURE DLAddShadowFile
    @ServerId int,
    @MyServerId int,
    @CompId int,
    @OriginalFileName nvarchar(1500),
    @CreationDate bigint,
    @DeviceType int,
    @OperationType int,
    @ProcessName nvarchar(1500),
    @Pid int,
    @StartingOffset bigint,
    @ShadowSize bigint,
    @IsFastRebuild bit,
    @IOErrFile image,
    @XMLFile ntext,
    @StoreId int,
    @Attributes int,
    @Status int,
    @ShadowId int output,
    @DelShadowId int output
AS
    SET NOCOUNT ON

    DECLARE @NetAddr nvarchar(50)
    DECLARE @Endpoint nvarchar(50)
    IF @MyServerId <> @ServerId BEGIN
        EXEC DLGetStationAddr @MyServerId, @CompId, @NetAddr output, @Endpoint output
        EXEC DLAddStation @ServerId, @NetAddr, @Endpoint, @CompId output
        EXEC DLStationScaned @ServerId, @CompId, 0, 0
    END

    SET @DelShadowId = 0

    SELECT TOP 1 @ShadowId = ShadowId
    FROM DLShadowFiles
    WHERE ServerId = @ServerId
    AND CompId = @CompId
    AND CreationDate = @CreationDate
    AND IsFastRebuild = @IsFastRebuild
    AND StoreId = @StoreId


    IF @@ROWCOUNT > 0
        RETURN 0

    INSERT INTO DLShadowFiles
    (ServerId, CompId, OriginalFileName, CreationDate,
    DeviceType, OperationType, ProcessName, Pid, StartingOffset,
    ShadowSize, IsFastRebuild, IOErrFile, XMLFile, StoreId,
    Attributes, Status)
    VALUES
    (@ServerId, @CompId, @OriginalFileName, @CreationDate,
    @DeviceType, @OperationType, @ProcessName, @Pid, @StartingOffset,
    @ShadowSize, @IsFastRebuild, @IOErrFile, @XMLFile, @StoreId,
    @Attributes, @Status)

    SET @ShadowId = @@IDENTITY


    IF @IsFastRebuild = 0 BEGIN
        SELECT TOP 1 @DelShadowId = ShadowId
        FROM DLShadowFiles
        WHERE ServerId = @ServerId
        AND CompId = @CompId
        AND CreationDate = @CreationDate
        AND IsFastRebuild = 1

        IF @@ROWCOUNT > 0
            RETURN 1
    END

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLBatchShadowAttr') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLBatchShadowAttr
GO
CREATE PROCEDURE DLBatchShadowAttr
    @Condition nvarchar(2000),
    @AttrIncl int,
    @AttrExcl int,
    @AttrAdd int,
    @AttrDel int,
    @NRecords int output
AS
    SET NOCOUNT ON

    DECLARE @Query nvarchar(4000)
    DECLARE @AttrStrIncl nvarchar(10)
    DECLARE @AttrStrExcl nvarchar(10)


    SET @Query = @Condition

    EXEC DLFilterGen 0, @Query output

    SET @AttrStrIncl = CAST(@AttrIncl AS nvarchar(10))
    SET @AttrStrExcl = CAST(@AttrExcl AS nvarchar(10))

    IF @Query <> N''
        SET @Query = N'AND ' + @Query

    SET @Query =
        CASE @AttrIncl
            WHEN 0 THEN N'sf.Attributes = 0 '
            ELSE N'sf.Attributes & ' + @AttrStrIncl + N' = ' + @AttrStrIncl + N' '
        END
        +
        CASE @AttrExcl
            WHEN 0 THEN N''
            ELSE N'AND sf.Attributes & ' + @AttrStrExcl + N' = 0 '
        END
        + @Query

    SET @Query = N'UPDATE DLShadowFiles
        SET Attributes = (Attributes & ~' + CAST(@AttrDel AS nvarchar(10)) +
        N') | ' + CAST(@AttrAdd AS nvarchar(10)) +
        N' FROM DLShadowFiles AS sf
        INNER JOIN DLStations AS st
            ON sf.ServerId = st.ServerId
            AND sf.CompId = st.CompId
        LEFT OUTER JOIN DLShadowFiles_Users AS sf_u
            ON sf.ServerId = sf_u.ServerId
            AND sf.ShadowId = sf_u.ShadowId
            AND sf_u.Flag <> 0
        LEFT OUTER JOIN DLUsers AS us
            ON sf.ServerId = us.ServerId
            AND sf_u.UserId = us.UserId
        WHERE ' + @Query

    EXEC(@Query)

    SET @NRecords = @@ROWCOUNT

    IF @@ERROR <> 0
        RETURN 1
    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLDelShadowFile') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLDelShadowFile
GO
CREATE PROCEDURE DLDelShadowFile
    @ServerId int,
    @ShadowId int
AS
    SET NOCOUNT ON
    DECLARE @Ret int
    DECLARE @UserId int

    EXEC DLDelData @ServerId, @ShadowId

    DECLARE UserCur CURSOR STATIC LOCAL FOR
    SELECT UserId FROM DLShadowFiles_Users
    WHERE ServerId = @ServerId
        AND ShadowId = @ShadowId

    OPEN UserCur
    DELETE FROM DLShadowFiles_Users
    WHERE ServerId = @ServerId
        AND ShadowId = @ShadowId

    WHILE 1=1 BEGIN
         FETCH NEXT FROM UserCur INTO @UserId
        IF @@fetch_status=-1
            BREAK
        IF @@fetch_status=-2
            CONTINUE
        EXEC DLDelUser @ServerId, @UserId
    END
    CLOSE UserCur
    DEALLOCATE UserCur

    DELETE FROM DLShadowFiles
    WHERE ServerId = @ServerId
        AND ShadowId = @ShadowId

    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLFilterShadow') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLFilterShadow
GO
CREATE PROCEDURE DLFilterShadow
    @Condition nvarchar(2000),
    @AttrIncl int,
    @AttrExcl int,
    @SortField int = NULL,
    @SortDir bit = 0,
    @PosFrom int = 0,
    @RecCount int = 0

AS
    SET NOCOUNT ON
    DECLARE @Ret int
    DECLARE @FieldName nvarchar(200)
    DECLARE @Query nvarchar(4000)
    DECLARE @SortOrder nvarchar(5)
    DECLARE @SortBackOrder nvarchar(5)
    DECLARE @RecordsCount int
    DECLARE @AttrStrIncl nvarchar(10)
    DECLARE @AttrStrExcl nvarchar(10)


    SET @Query = @Condition

    EXEC DLFilterGen 0, @Query output

    SET @AttrStrIncl = CAST(@AttrIncl AS nvarchar(10))
    SET @AttrStrExcl = CAST(@AttrExcl AS nvarchar(10))

    IF @Query <> N''
        SET @Query = N'AND ' + @Query

    SET @Query =
        CASE @AttrIncl
            WHEN 0 THEN N'sf.Attributes = 0 '
            ELSE N'sf.Attributes & ' + @AttrStrIncl + N' = ' + @AttrStrIncl + N' '
        END
        +
        CASE @AttrExcl
            WHEN 0 THEN N''
            ELSE N'AND sf.Attributes & ' + @AttrStrExcl + N' = 0 '
        END
        + @Query

    SET @Query =
    N'FROM DLShadowFiles AS sf
    INNER JOIN DLStations AS st
        ON sf.ServerId = st.ServerId
        AND sf.CompId = st.CompId
    LEFT OUTER JOIN DLShadowFiles_Users AS sf_u
        ON sf.ServerId = sf_u.ServerId
        AND sf.ShadowId = sf_u.ShadowId
        AND sf_u.Flag <> 0
    LEFT OUTER JOIN DLUsers AS us
        ON sf.ServerId = us.ServerId
        AND sf_u.UserId = us.UserId
    WHERE ' + @Query

    IF @SortField IS NULL BEGIN
        SET @Query = N'SELECT COUNT(DISTINCT sf.ShadowId) ' + @Query
        GOTO exec_query
    END

    EXEC @Ret = DLGetFieldById 0, @SortField, @FieldName output
    IF @Ret <> 0 SET @FieldName = N'1'

    IF ISNULL(@SortDir, 0) = 0 BEGIN
        SET @SortOrder = 'ASC'
        SET @SortBackOrder = 'DESC'
    END
    ELSE BEGIN
        SET @SortOrder = 'DESC'
        SET @SortBackOrder = 'ASC'
    END

    SET @RecordsCount = (SELECT COUNT(*) FROM DLShadowFiles)

    IF ISNULL(@PosFrom, 0) <= 0
        SET @PosFrom = 1

    IF ISNULL(@RecCount, 0) < 0
        RETURN 1

    IF @PosFrom > @RecordsCount BEGIN
        SET @RecCount = 0
    END
    ELSE IF @PosFrom + @RecCount > @RecordsCount BEGIN
        SET @RecCount = @RecordsCount - @PosFrom + 1
    END


    SET @Query =
    N'sf.ServerId AS ServerId,
    sf.ShadowId AS ShadowId,
    sf.Attributes AS Attributes,
    sf.ShadowSize AS ShadowSize,
    st.NetworkAddr AS NetworkAddr,
    ' + @FieldName + N' AS SortField ' + @Query

    IF @RecCount IS NULL BEGIN
        SET @Query = N'SELECT DISTINCT ' + @Query

        SET @Query = N'SELECT S1.ServerId AS ServerId, S1.ShadowId AS ShadowId, S1.Attributes AS Attributes, S1.ShadowSize AS ShadowSize, S1.NetworkAddr AS NetworkAddr, sf.XMLFile AS XMLFile
                    FROM (' + @Query + N') AS S1,
                    DLShadowFiles as sf
                    WHERE S1.ServerId = sf.ServerId
                    AND S1.ShadowId = sf.ShadowId
                    ORDER BY S1.SortField ' + @SortOrder + N', S1.ShadowId '+@SortOrder
    END
    ELSE BEGIN
        SET @Query = N'SELECT DISTINCT TOP ' + CAST(@PosFrom+@RecCount-1 AS nvarchar(10)) + N' ' +
                    @Query +
                    N' ORDER BY ' + @FieldName + N' ' + @SortOrder + N', sf.ShadowId ' + @SortOrder

        SET @Query = N'SELECT TOP '+ CAST(@RecCount AS nvarchar(10)) + N' *
                    FROM (' + @Query + N') AS S1
                    ORDER BY SortField ' + @SortBackOrder + N', ShadowId '+ @SortBackOrder

        SET @Query = N'SELECT S2.ServerId AS ServerId, S2.ShadowId AS ShadowId, S2.Attributes AS Attributes, S2.ShadowSize AS ShadowSize, S2.NetworkAddr AS NetworkAddr, sf1.XMLFile AS XMLFile
                    FROM (' + @Query + N') AS S2,
                    DLShadowFiles AS sf1
                    WHERE S2.ServerId = sf1.ServerId
                    AND S2.ShadowId = sf1.ShadowId
                    ORDER BY S2.SortField' + N' '+ @SortOrder + N', S2.ShadowId ' + @SortOrder
    END

exec_query:

    SET NOCOUNT OFF
    EXEC(@Query)
    SET NOCOUNT ON
    IF @@ERROR <> 0
        RETURN 1
    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLGetShadowAttr') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLGetShadowAttr
GO
CREATE PROCEDURE DLGetShadowAttr
    @ServerId int,
    @ShadowId int,
    @Attr int output
AS
    SET NOCOUNT ON
    SELECT @Attr = Attributes
        FROM DLShadowFiles
        WHERE ServerId = @ServerId
        AND ShadowId = @ShadowId

    IF @@ROWCOUNT = 0
        RETURN 1
    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLSetShadowAttr') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLSetShadowAttr
GO
CREATE PROCEDURE DLSetShadowAttr
    @ServerId int,
    @ShadowId int,
    @AttrAdd int,
    @AttrDel int
AS
    SET NOCOUNT ON
    UPDATE DLShadowFiles
        SET Attributes = (Attributes & ~@AttrDel) | @AttrAdd
        WHERE ServerId = @ServerId
        AND ShadowId = @ShadowId
    IF @@ROWCOUNT = 0
        RETURN 1
    RETURN 0
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLPreAddShadowLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLPreAddShadowLog
GO

CREATE PROCEDURE DLPreAddShadowLog
    @Size int,
    @NeedDelete int,
    @TimeBefore bigint = NULL,
    @AttrIncl int,
    @AttrExcl int,
    @AttrSetExcl int,
    @AttrSet int
AS
    SET NOCOUNT ON
    DECLARE @RecordsCount int
    DECLARE @DeleteCount int
    DECLARE @ServerId int
    DECLARE @ShadowId int
    DECLARE @Ret int

    SET @Ret = 0

    SELECT @RecordsCount = COUNT(*)
    FROM DLShadowFiles
    WHERE Attributes & @AttrIncl = @AttrIncl
    AND Attributes & @AttrExcl = 0

    IF @RecordsCount <= @Size
        RETURN 1

    IF @NeedDelete = 0
        RETURN @Ret

    SET @DeleteCount = @RecordsCount - @Size


    IF @TimeBefore IS NULL BEGIN
        DECLARE OverwriteCur CURSOR FOR
            SELECT ServerId, ShadowId FROM DLShadowFiles
            WHERE Attributes & @AttrIncl = @AttrIncl
            AND Attributes & (@AttrExcl | @AttrSetExcl) = 0
            ORDER BY CreationDate ASC, ShadowId ASC
    END
    ELSE BEGIN
        DECLARE OverwriteCur CURSOR FOR
            SELECT ServerId, ShadowId FROM DLShadowFiles
            WHERE Attributes & @AttrIncl = @AttrIncl
            AND Attributes & (@AttrExcl | @AttrSetExcl) = 0
            AND CreationDate < @TimeBefore
            ORDER BY CreationDate ASC, ShadowId ASC
    END

    OPEN OverwriteCur

    WHILE @DeleteCount > 0 BEGIN
         FETCH NEXT FROM OverwriteCur INTO @ServerId, @ShadowId
        IF @@fetch_status=-1
            BREAK
        SET @DeleteCount = @DeleteCount - 1

        IF @@fetch_status=-2
            CONTINUE

        UPDATE DLShadowFiles
            SET Attributes = @AttrSet
            WHERE ServerId = @ServerId
            AND ShadowId = @ShadowId

        IF @@ROWCOUNT > 0
            SET @Ret = @Ret | 2
    END

    CLOSE OverwriteCur
    DEALLOCATE OverwriteCur

    IF @DeleteCount <= 0
        SET @Ret = @Ret | 1

    RETURN @Ret
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'DLPostAddDeletedLog') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE DLPostAddDeletedLog
GO

CREATE PROCEDURE DLPostAddDeletedLog
    @Size int,
    @NeedDelete int,
    @TimeBefore bigint = NULL,
    @AttrIncl int,
    @AttrExcl int,
    @AttrSetIncl int,
    @AttrSet int
AS
    SET NOCOUNT ON
    DECLARE @RecordsCount int
    DECLARE @DeleteCount int
    DECLARE @ServerId int
    DECLARE @ShadowId int
    DECLARE @Ret int
    DECLARE @TmpRet int

    SET @Ret = 0

    IF @Size <> 0 BEGIN
        EXEC @Ret = DLPreAddShadowLog @Size, @NeedDelete, @TimeBefore, @AttrIncl, @AttrExcl, 0, @AttrSet

        SELECT @RecordsCount = COUNT(*)
        FROM DLShadowFiles
        WHERE Attributes & @AttrIncl = @AttrIncl
        AND Attributes & @AttrExcl = 0

        SET @DeleteCount = @RecordsCount - @Size
    END ELSE
        SET @DeleteCount = 0

    DECLARE OverwriteCur CURSOR FOR
        SELECT ServerId, ShadowId FROM DLShadowFiles
        WHERE Attributes & (@AttrIncl | @AttrSetIncl) = (@AttrIncl | @AttrSetIncl)
        AND Attributes & @AttrExcl = 0
        ORDER BY CreationDate DESC

    OPEN OverwriteCur

    WHILE 1=1 BEGIN
         FETCH NEXT FROM OverwriteCur INTO @ServerId, @ShadowId
        IF @@fetch_status=-1
            BREAK

        IF @@fetch_status=-2 BEGIN
            SET @DeleteCount = @DeleteCount - 1
            CONTINUE
        END

        IF @DeleteCount <= 0 BEGIN
            EXEC @TmpRet = DLSetShadowAttr @ServerId, @ShadowId, 0, @AttrSetIncl
            IF @TmpRet = 0
                SET @Ret = @Ret | 2
        END
        ELSE BEGIN
            UPDATE DLShadowFiles
                SET Attributes = @AttrSet
                WHERE ServerId = @ServerId
                AND ShadowId = @ShadowId

            IF @@ROWCOUNT > 0
                SET @Ret = @Ret | 6

            SET @DeleteCount = @DeleteCount - 1
        END

    END

    CLOSE OverwriteCur
    DEALLOCATE OverwriteCur

    IF @DeleteCount <= 0
        SET @Ret = @Ret | 1

    RETURN @Ret
GO